{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "engine = create_engine('sqlite:///:memory:')\n",
    "\n",
    "Session = sessionmaker(bind=engine)\n",
    "\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "from sqlalchemy import Column, Integer, Numeric, String, DateTime, ForeignKey, Boolean\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import relationship, backref\n",
    "\n",
    "\n",
    "Base = declarative_base()\n",
    "\n",
    "\n",
    "class Cookie(Base):\n",
    "    __tablename__ = 'cookies'\n",
    "\n",
    "    cookie_id = Column(Integer, primary_key=True)\n",
    "    cookie_name = Column(String(50), index=True)\n",
    "    cookie_recipe_url = Column(String(255))\n",
    "    cookie_sku = Column(String(55))\n",
    "    quantity = Column(Integer())\n",
    "    unit_cost = Column(Numeric(12, 2))\n",
    "    \n",
    "    def __init__(self, name, recipe_url=None, sku=None, quantity=0, unit_cost=0.00):\n",
    "        self.cookie_name = name\n",
    "        self.cookie_recipe_url = recipe_url\n",
    "        self.cookie_sku = sku\n",
    "        self.quantity = quantity\n",
    "        self.unit_cost = unit_cost\n",
    "        \n",
    "    def __repr__(self):\n",
    "        return \"Cookie(cookie_name='{self.cookie_name}', \" \\\n",
    "                       \"cookie_recipe_url='{self.cookie_recipe_url}', \" \\\n",
    "                       \"cookie_sku='{self.cookie_sku}', \" \\\n",
    "                       \"quantity={self.quantity}, \" \\\n",
    "                       \"unit_cost={self.unit_cost})\".format(self=self)\n",
    "    \n",
    "    \n",
    "class User(Base):\n",
    "    __tablename__ = 'users'\n",
    "    \n",
    "    user_id = Column(Integer(), primary_key=True)\n",
    "    username = Column(String(15), nullable=False, unique=True)\n",
    "    email_address = Column(String(255), nullable=False)\n",
    "    phone = Column(String(20), nullable=False)\n",
    "    password = Column(String(25), nullable=False)\n",
    "    created_on = Column(DateTime(), default=datetime.now)\n",
    "    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)\n",
    "    \n",
    "    def __init__(self, username, email_address, phone, password):\n",
    "        self.username = username\n",
    "        self.email_address = email_address\n",
    "        self.phone = phone\n",
    "        self.password = password\n",
    "        \n",
    "    def __repr__(self):\n",
    "        return \"User(username='{self.username}', \" \\\n",
    "                     \"email_address='{self.email_address}', \" \\\n",
    "                     \"phone='{self.phone}', \" \\\n",
    "                     \"password='{self.password}')\".format(self=self)\n",
    "    \n",
    "\n",
    "class Order(Base):\n",
    "    __tablename__ = 'orders'\n",
    "    order_id = Column(Integer(), primary_key=True)\n",
    "    user_id = Column(Integer(), ForeignKey('users.user_id'))\n",
    "    shipped = Column(Boolean(), default=False)\n",
    "    \n",
    "    user =  relationship(\"User\", backref=backref('orders', order_by=order_id))\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return \"Order(user_id={self.user_id}, \" \\\n",
    "                      \"shipped={self.shipped})\".format(self=self)\n",
    "\n",
    "\n",
    "class LineItem(Base):\n",
    "    __tablename__ = 'line_items'\n",
    "    line_item_id = Column(Integer(), primary_key=True)\n",
    "    order_id = Column(Integer(), ForeignKey('orders.order_id'))\n",
    "    cookie_id = Column(Integer(), ForeignKey('cookies.cookie_id'))\n",
    "    quantity = Column(Integer())\n",
    "    extended_cost = Column(Numeric(12, 2))\n",
    "    \n",
    "    order = relationship(\"Order\", backref=backref('line_items', order_by=line_item_id))\n",
    "    cookie = relationship(\"Cookie\", uselist=False)\n",
    "\n",
    "    def __repr__(self):\n",
    "        return \"LineItems(order_id={self.order_id}, \" \\\n",
    "                          \"cookie_id={self.cookie_id}, \" \\\n",
    "                          \"quantity={self.quantity}, \" \\\n",
    "                          \"extended_cost={self.extended_cost})\".format(\n",
    "                    self=self)    \n",
    "    \n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "cc_cookie = Cookie('chocolate chip', \n",
    "                   'http://some.aweso.me/cookie/recipe.html', \n",
    "                   'CC01', 12, 0.50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import inspect\n",
    "insp = inspect(cc_cookie)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " transient: True\n",
      "   pending: False\n",
      "persistent: False\n",
      "  detached: False\n"
     ]
    }
   ],
   "source": [
    "for state in ['transient', 'pending', 'persistent', 'detached']:\n",
    "    print('{:>10}: {}'.format(state, getattr(insp, state)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "session.add(cc_cookie)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " transient: False\n",
      "   pending: True\n",
      "persistent: False\n",
      "  detached: False\n"
     ]
    }
   ],
   "source": [
    "for state in ['transient','pending','persistent','detached']:\n",
    "    print('{:>10}: {}'.format(state, getattr(insp, state)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " transient: False\n",
      "   pending: False\n",
      "persistent: True\n",
      "  detached: False\n"
     ]
    }
   ],
   "source": [
    "for state in ['transient','pending','persistent','detached']:\n",
    "    print('{:>10}: {}'.format(state, getattr(insp, state)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "session.expunge(cc_cookie)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " transient: False\n",
      "   pending: False\n",
      "persistent: False\n",
      "  detached: True\n"
     ]
    }
   ],
   "source": [
    "for state in ['transient','pending','persistent','detached']:\n",
    "    print('{:>10}: {}'.format(state, getattr(insp, state)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "session.add(cc_cookie)\n",
    "cc_cookie.cookie_name = 'Change chocolate chip'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "insp.modified"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "cookie_name: Change chocolate chip\n",
      "History: History(added=['Change chocolate chip'], unchanged=(), deleted=())\n",
      "\n"
     ]
    }
   ],
   "source": [
    "for attr, attr_state in insp.attrs.items():\n",
    "    if attr_state.history.has_changes():\n",
    "        print('{}: {}'.format(attr, attr_state.value))\n",
    "        print('History: {}\\n'.format(attr_state.history))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/sql/sqltypes.py:565: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.\n",
      "  'storage.' % (dialect.name, dialect.driver))\n"
     ]
    }
   ],
   "source": [
    "dcc = Cookie('dark chocolate chip',\n",
    "             'http://some.aweso.me/cookie/recipe_dark.html',\n",
    "             'CC02', 1, 0.75)\n",
    "session.add(dcc)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "MultipleResultsFound",
     "evalue": "Multiple rows were found for one()",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mMultipleResultsFound\u001b[0m                      Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-16-d88068ecde4b>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mresults\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mCookie\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mone\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc\u001b[0m in \u001b[0;36mone\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   2480\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2481\u001b[0m             raise orm_exc.MultipleResultsFound(\n\u001b[0;32m-> 2482\u001b[0;31m                 \"Multiple rows were found for one()\")\n\u001b[0m\u001b[1;32m   2483\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2484\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mscalar\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mMultipleResultsFound\u001b[0m: Multiple rows were found for one()"
     ]
    }
   ],
   "source": [
    "results = session.query(Cookie).one()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "We found too many cookies... is that even possible?\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.orm.exc import MultipleResultsFound\n",
    "try:\n",
    "    results = session.query(Cookie).one()\n",
    "except MultipleResultsFound as exc:\n",
    "    print('We found too many cookies... is that even possible?')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Cookie(cookie_name='Change chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe.html', cookie_sku='CC01', quantity=12, unit_cost=0.50),\n",
       " Cookie(cookie_name='dark chocolate chip', cookie_recipe_url='http://some.aweso.me/cookie/recipe_dark.html', cookie_sku='CC02', quantity=1, unit_cost=0.75)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Cookie).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cookiemon = User('cookiemon', 'mon@cookie.com', '111-111-1111', 'password')\n",
    "session.add(cookiemon)\n",
    "o1 = Order()\n",
    "o1.user = cookiemon\n",
    "session.add(o1)\n",
    "\n",
    "cc = session.query(Cookie).filter(Cookie.cookie_name == \n",
    "                                  \"Change chocolate chip\").one()\n",
    "line1 = LineItem(order=o1, cookie=cc, quantity=2, extended_cost=1.00)\n",
    "\n",
    "session.add(line1)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "DetachedInstanceError",
     "evalue": "Parent instance <Order at 0x10dc6ba50> is not bound to a Session; lazy load operation of attribute 'line_items' cannot proceed",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mDetachedInstanceError\u001b[0m                     Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-20-233bbca5c715>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0morder\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mOrder\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfirst\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexpunge\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0morder\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 3\u001b[0;31m \u001b[0morder\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mline_items\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/attributes.pyc\u001b[0m in \u001b[0;36m__get__\u001b[0;34m(self, instance, owner)\u001b[0m\n\u001b[1;32m    235\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mdict_\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    236\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 237\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mimpl\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mget\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0minstance_state\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0minstance\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdict_\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    238\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    239\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/attributes.pyc\u001b[0m in \u001b[0;36mget\u001b[0;34m(self, state, dict_, passive)\u001b[0m\n\u001b[1;32m    576\u001b[0m                     \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcallable_\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstate\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mpassive\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    577\u001b[0m                 \u001b[0;32melif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcallable_\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 578\u001b[0;31m                     \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcallable_\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstate\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mpassive\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    579\u001b[0m                 \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    580\u001b[0m                     \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mATTR_EMPTY\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/strategies.pyc\u001b[0m in \u001b[0;36m_load_for_state\u001b[0;34m(self, state, passive)\u001b[0m\n\u001b[1;32m    499\u001b[0m                 \u001b[0;34m\"Parent instance %s is not bound to a Session; \"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    500\u001b[0m                 \u001b[0;34m\"lazy load operation of attribute '%s' cannot proceed\"\u001b[0m \u001b[0;34m%\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 501\u001b[0;31m                 \u001b[0;34m(\u001b[0m\u001b[0morm_util\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstate_str\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstate\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    502\u001b[0m             )\n\u001b[1;32m    503\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDetachedInstanceError\u001b[0m: Parent instance <Order at 0x10dc6ba50> is not bound to a Session; lazy load operation of attribute 'line_items' cannot proceed"
     ]
    }
   ],
   "source": [
    "order = session.query(Order).first()\n",
    "session.expunge(order)\n",
    "order.line_items.all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "IntegrityError",
     "evalue": "(raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.IntegrityError) UNIQUE constraint failed: users.username [SQL: u'INSERT INTO users (username, email_address, phone, password, created_on, updated_on) VALUES (?, ?, ?, ?, ?, ?)'] [parameters: ('cookiemon', 'mon@cookie.com', '111-111-1111', 'password', '2015-08-11 20:29:22.988774', '2015-08-11 20:29:22.988786')]",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mIntegrityError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-21-1831fb6964aa>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      7\u001b[0m cc = session.query(Cookie).filter(Cookie.cookie_name == \n\u001b[0;32m----> 8\u001b[0;31m                                   \"Change chocolate chip\").one()\n\u001b[0m\u001b[1;32m      9\u001b[0m \u001b[0mline1\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mLineItem\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0morder\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mo1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcookie\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquantity\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mextended_cost\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m1.00\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     10\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc\u001b[0m in \u001b[0;36mone\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   2471\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2472\u001b[0m         \"\"\"\n\u001b[0;32m-> 2473\u001b[0;31m         \u001b[0mret\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mlist\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2474\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2475\u001b[0m         \u001b[0ml\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mret\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc\u001b[0m in \u001b[0;36m__iter__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   2513\u001b[0m         \u001b[0mcontext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0muse_labels\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2514\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_autoflush\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_populate_existing\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2515\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_autoflush\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2516\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_and_instances\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcontext\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2517\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_autoflush\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1290\u001b[0m                     \u001b[0;34m\"consider using a session.no_autoflush block if this \"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1291\u001b[0m                     \"flush is occurring prematurely\")\n\u001b[0;32m-> 1292\u001b[0;31m                 \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mraise_from_cause\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1293\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1294\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mrefresh\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0minstance\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mattribute_names\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mlockmode\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc\u001b[0m in \u001b[0;36mraise_from_cause\u001b[0;34m(exception, exc_info)\u001b[0m\n\u001b[1;32m    197\u001b[0m             \u001b[0mexc_info\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    198\u001b[0m         \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 199\u001b[0;31m         \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    200\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    201\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mpy3k\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_autoflush\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   1280\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mautoflush\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_flushing\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1281\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1282\u001b[0;31m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mflush\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1283\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0msa_exc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mStatementError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1284\u001b[0m                 \u001b[0;31m# note we are reraising StatementError as opposed to\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36mflush\u001b[0;34m(self, objects)\u001b[0m\n\u001b[1;32m   2002\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2003\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_flushing\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2004\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_flush\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mobjects\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2005\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2006\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_flushing\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_flush\u001b[0;34m(self, objects)\u001b[0m\n\u001b[1;32m   2120\u001b[0m         \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2121\u001b[0m             \u001b[0;32mwith\u001b[0m \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msafe_reraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2122\u001b[0;31m                 \u001b[0mtransaction\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrollback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0m_capture_exception\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2123\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2124\u001b[0m     def bulk_save_objects(\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.pyc\u001b[0m in \u001b[0;36m__exit__\u001b[0;34m(self, type_, value, traceback)\u001b[0m\n\u001b[1;32m     58\u001b[0m             \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     59\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exc_info\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mNone\u001b[0m   \u001b[0;31m# remove potential circular references\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 60\u001b[0;31m             \u001b[0mcompat\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     61\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     62\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exc_info\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mNone\u001b[0m   \u001b[0;31m# remove potential circular references\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_flush\u001b[0;34m(self, objects)\u001b[0m\n\u001b[1;32m   2084\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_warn_on_events\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mTrue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2085\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2086\u001b[0;31m                 \u001b[0mflush_context\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2087\u001b[0m             \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2088\u001b[0m                 \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_warn_on_events\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    371\u001b[0m                     \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdependencies\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    372\u001b[0m                     postsort_actions):\n\u001b[0;32m--> 373\u001b[0;31m                 \u001b[0mrec\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    374\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    375\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mfinalize_flush_changes\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, uow)\u001b[0m\n\u001b[1;32m    530\u001b[0m                              uow.states_for_mapper_hierarchy(\n\u001b[1;32m    531\u001b[0m                                  self.mapper, False, False),\n\u001b[0;32m--> 532\u001b[0;31m                              \u001b[0muow\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    533\u001b[0m                              )\n\u001b[1;32m    534\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/persistence.pyc\u001b[0m in \u001b[0;36msave_obj\u001b[0;34m(base_mapper, states, uowtransaction, single)\u001b[0m\n\u001b[1;32m    172\u001b[0m         _emit_insert_statements(base_mapper, uowtransaction,\n\u001b[1;32m    173\u001b[0m                                 \u001b[0mcached_connections\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 174\u001b[0;31m                                 mapper, table, insert)\n\u001b[0m\u001b[1;32m    175\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    176\u001b[0m     _finalize_insert_update_commands(\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/persistence.pyc\u001b[0m in \u001b[0;36m_emit_insert_statements\u001b[0;34m(base_mapper, uowtransaction, cached_connections, mapper, table, insert, bookkeeping)\u001b[0m\n\u001b[1;32m    777\u001b[0m                 \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    778\u001b[0m                     \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcached_connections\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0;31m\\\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 779\u001b[0;31m                         \u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    780\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    781\u001b[0m                 \u001b[0mprimary_key\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcontext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minserted_primary_key\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, object, *multiparams, **params)\u001b[0m\n\u001b[1;32m    912\u001b[0m                 type(object))\n\u001b[1;32m    913\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 914\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mmeth\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    915\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    916\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_function\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mfunc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/sql/elements.pyc\u001b[0m in \u001b[0;36m_execute_on_connection\u001b[0;34m(self, connection, multiparams, params)\u001b[0m\n\u001b[1;32m    321\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    322\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_execute_on_connection\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 323\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_clauseelement\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    324\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    325\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0munique_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0moptionaldict\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_clauseelement\u001b[0;34m(self, elem, multiparams, params)\u001b[0m\n\u001b[1;32m   1008\u001b[0m             \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1009\u001b[0m             \u001b[0mdistilled_params\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1010\u001b[0;31m             \u001b[0mcompiled_sql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdistilled_params\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1011\u001b[0m         )\n\u001b[1;32m   1012\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1144\u001b[0m                 \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1145\u001b[0m                 \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1146\u001b[0;31m                 context)\n\u001b[0m\u001b[1;32m   1147\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1148\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m \u001b[0;32mor\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_has_events\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_handle_dbapi_exception\u001b[0;34m(self, e, statement, parameters, cursor, context)\u001b[0m\n\u001b[1;32m   1339\u001b[0m                 util.raise_from_cause(\n\u001b[1;32m   1340\u001b[0m                     \u001b[0msqlalchemy_exception\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1341\u001b[0;31m                     \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1342\u001b[0m                 )\n\u001b[1;32m   1343\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc\u001b[0m in \u001b[0;36mraise_from_cause\u001b[0;34m(exception, exc_info)\u001b[0m\n\u001b[1;32m    197\u001b[0m             \u001b[0mexc_info\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    198\u001b[0m         \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mexc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 199\u001b[0;31m         \u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtype\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexception\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mexc_tb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    200\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    201\u001b[0m \u001b[0;32mif\u001b[0m \u001b[0mpy3k\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc\u001b[0m in \u001b[0;36m_execute_context\u001b[0;34m(self, dialect, constructor, statement, parameters, *args)\u001b[0m\n\u001b[1;32m   1137\u001b[0m                         \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1138\u001b[0m                         \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1139\u001b[0;31m                         context)\n\u001b[0m\u001b[1;32m   1140\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1141\u001b[0m             self._handle_dbapi_exception(\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc\u001b[0m in \u001b[0;36mdo_execute\u001b[0;34m(self, cursor, statement, parameters, context)\u001b[0m\n\u001b[1;32m    448\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    449\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 450\u001b[0;31m         \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparameters\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    451\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    452\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mdo_execute_no_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcontext\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mIntegrityError\u001b[0m: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.IntegrityError) UNIQUE constraint failed: users.username [SQL: u'INSERT INTO users (username, email_address, phone, password, created_on, updated_on) VALUES (?, ?, ?, ?, ?, ?)'] [parameters: ('cookiemon', 'mon@cookie.com', '111-111-1111', 'password', '2015-08-11 20:29:22.988774', '2015-08-11 20:29:22.988786')]"
     ]
    }
   ],
   "source": [
    "cookiemon = User('cookiemon', 'mon@cookie.com', '111-111-1111', 'password')\n",
    "session.add(cookiemon)\n",
    "o1 = Order()\n",
    "o1.user = cookiemon\n",
    "session.add(o1)\n",
    "\n",
    "cc = session.query(Cookie).filter(Cookie.cookie_name == \n",
    "                                  \"Change chocolate chip\").one()\n",
    "line1 = LineItem(order=o1, cookie=cc, quantity=2, extended_cost=1.00)\n",
    "\n",
    "session.add(line1)\n",
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "ename": "InvalidRequestError",
     "evalue": "This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.IntegrityError) UNIQUE constraint failed: users.username [SQL: u'INSERT INTO users (username, email_address, phone, password, created_on, updated_on) VALUES (?, ?, ?, ?, ?, ?)'] [parameters: ('cookiemon', 'mon@cookie.com', '111-111-1111', 'password', '2015-08-11 20:29:22.988774', '2015-08-11 20:29:22.988786')]",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mInvalidRequestError\u001b[0m                       Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-22-fa9a7ba82c37>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mcookies\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mCookie\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc\u001b[0m in \u001b[0;36mall\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   2397\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2398\u001b[0m         \"\"\"\n\u001b[0;32m-> 2399\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mlist\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2400\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2401\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0m_generative\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0m_no_clauseelement_condition\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc\u001b[0m in \u001b[0;36m__iter__\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m   2514\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_autoflush\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_populate_existing\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2515\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msession\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_autoflush\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2516\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execute_and_instances\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcontext\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2517\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2518\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_connection_from_session\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc\u001b[0m in \u001b[0;36m_execute_and_instances\u001b[0;34m(self, querycontext)\u001b[0m\n\u001b[1;32m   2527\u001b[0m             \u001b[0mmapper\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_bind_mapper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2528\u001b[0m             \u001b[0mclause\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mquerycontext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2529\u001b[0;31m             close_with_result=True)\n\u001b[0m\u001b[1;32m   2530\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2531\u001b[0m         \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquerycontext\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_params\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/query.pyc\u001b[0m in \u001b[0;36m_connection_from_session\u001b[0;34m(self, **kw)\u001b[0m\n\u001b[1;32m   2518\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_connection_from_session\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2519\u001b[0m         conn = self.session.connection(\n\u001b[0;32m-> 2520\u001b[0;31m             **kw)\n\u001b[0m\u001b[1;32m   2521\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execution_options\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2522\u001b[0m             \u001b[0mconn\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecution_options\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m**\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_execution_options\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36mconnection\u001b[0;34m(self, mapper, clause, bind, close_with_result, execution_options, **kw)\u001b[0m\n\u001b[1;32m    880\u001b[0m         return self._connection_for_bind(bind,\n\u001b[1;32m    881\u001b[0m                                          \u001b[0mclose_with_result\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mclose_with_result\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 882\u001b[0;31m                                          execution_options=execution_options)\n\u001b[0m\u001b[1;32m    883\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    884\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_connection_for_bind\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_connection_for_bind\u001b[0;34m(self, engine, execution_options, **kw)\u001b[0m\n\u001b[1;32m    885\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mtransaction\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    886\u001b[0m             return self.transaction._connection_for_bind(\n\u001b[0;32m--> 887\u001b[0;31m                 engine, execution_options)\n\u001b[0m\u001b[1;32m    888\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    889\u001b[0m             \u001b[0mconn\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcontextual_connect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_connection_for_bind\u001b[0;34m(self, bind, execution_options)\u001b[0m\n\u001b[1;32m    311\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    312\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_connection_for_bind\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbind\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexecution_options\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 313\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_assert_active\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    314\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    315\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mbind\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_connections\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/Users/jasomyer/.virtualenvs/sa-book/lib/python2.7/site-packages/sqlalchemy/orm/session.pyc\u001b[0m in \u001b[0;36m_assert_active\u001b[0;34m(self, prepared_ok, rollback_ok, deactive_ok, closed_msg)\u001b[0m\n\u001b[1;32m    212\u001b[0m                         \u001b[0;34m\"first issue Session.rollback().\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    213\u001b[0m                         \u001b[0;34m\" Original exception was: %s\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 214\u001b[0;31m                         \u001b[0;34m%\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_rollback_exception\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    215\u001b[0m                     )\n\u001b[1;32m    216\u001b[0m                 \u001b[0;32melif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mdeactive_ok\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mInvalidRequestError\u001b[0m: This Session's transaction has been rolled back due to a previous exception during flush. To begin a new transaction with this Session, first issue Session.rollback(). Original exception was: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely) (sqlite3.IntegrityError) UNIQUE constraint failed: users.username [SQL: u'INSERT INTO users (username, email_address, phone, password, created_on, updated_on) VALUES (?, ?, ?, ?, ?, ?)'] [parameters: ('cookiemon', 'mon@cookie.com', '111-111-1111', 'password', '2015-08-11 20:29:22.988774', '2015-08-11 20:29:22.988786')]"
     ]
    }
   ],
   "source": [
    "cookies = session.query(Cookie).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "session.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ERROR: UNIQUE constraint failed: users.username\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.exc import IntegrityError\n",
    "try:\n",
    "    cookiemon = User('cookiemon', 'mon@cookie.com', '111-111-1111', 'password')\n",
    "    session.add(cookiemon)\n",
    "    o1 = Order()\n",
    "    o1.user = cookiemon\n",
    "    session.add(o1)\n",
    "\n",
    "    cc = session.query(Cookie).filter(Cookie.cookie_name == \n",
    "                                  \"Change chocolate chip\").one()\n",
    "    line1 = LineItem(order=o1, cookie=cc, quantity=2, extended_cost=1.00)\n",
    "\n",
    "    session.add(line1)\n",
    "    session.commit()\n",
    "except IntegrityError as error:\n",
    "    print('ERROR: {}'.format(error.orig.message))\n",
    "    session.rollback()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Order(user_id=1, shipped=False)]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.query(Order).all()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
